package com.blx.utils.pagination.interceptor;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.builder.xml.dynamic.ForEachSqlNode;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.apache.log4j.Logger;

import com.blx.utils.pagination.Pagination;
import com.blx.utils.pagination.dialect.Dialect;

/**
 * <p>
 * 功能：
 * setParameters 设置参数
 * getCount 查询总记录数
 * generatePageSql 生成分页查询SQL
 * </p>
 *
 * @since JDK 1.5
 * @version 1.0 2012-12-15
 * @author zhangxingkai
 */
public class SQLHelp {
	protected static Logger logger = Logger.getLogger(PaginationInterceptor.class);

	/**
	 * 对SQL参数(?)设值, 参考org.apache.ibatis.executor.parameter.DefaultParameterHandler。
	 *
	 * @param ps              表示预编译的 SQL 语句的对象。
	 * @param mappedStatement MappedStatement
	 * @param boundSql        SQL
	 * @param parameterObject 参数对象
	 * @throws java.sql.SQLException 数据库异常
	 */
	@SuppressWarnings("unchecked")
	public static void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject)
			throws SQLException {
		ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
		List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
		if (parameterMappings != null) {
			Configuration configuration = mappedStatement.getConfiguration();
			TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
			MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject);
			for (int i = 0; i < parameterMappings.size(); i++) {
				ParameterMapping parameterMapping = parameterMappings.get(i);
				if (parameterMapping.getMode() != ParameterMode.OUT) {
					Object value;
					String propertyName = parameterMapping.getProperty();
					PropertyTokenizer prop = new PropertyTokenizer(propertyName);
					if (parameterObject == null) {
						value = null;
					} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
						value = parameterObject;
					} else if (boundSql.hasAdditionalParameter(propertyName)) {
						value = boundSql.getAdditionalParameter(propertyName);
					} else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName())) {
						value = boundSql.getAdditionalParameter(prop.getName());
						if (value != null) {
							value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));
						}
					} else {
						value = metaObject == null ? null : metaObject.getValue(propertyName);
					}
					@SuppressWarnings("rawtypes")
					TypeHandler typeHandler = parameterMapping.getTypeHandler();
					if (typeHandler == null) {
						throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement "
								+ mappedStatement.getId());
					}
					typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());
				}
			}
		}
	}

	/**
	 * 查询总纪录数。
	 *
	 * @param sql             SQL语句
	 * @param connection      数据库连接
	 * @param mappedStatement mapped
	 * @param parameterObject 参数
	 * @param boundSql        boundSql
	 * @return 总记录数
	 * @throws SQLException sql查询错误
	 */
	public static int getCount(final String sql, final Connection connection, final MappedStatement mappedStatement, final Object parameterObject,
			final BoundSql boundSql) {
		final String countSql = "select count(1) from (" + excludeOrderBy(sql) + ") as tmp_count";
		logger.debug("Pagination TotalCount SQL: " + countSql);

		PreparedStatement countStmt = null;
		ResultSet rs = null;
		try {
			connection.setReadOnly(true);
			connection.setAutoCommit(true);
			countStmt = connection.prepareStatement(countSql);
			BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject);
			SQLHelp.setParameters(countStmt, mappedStatement, countBS, parameterObject);
			rs = countStmt.executeQuery();
			int count = 0;
			if (rs.next()) {
				count = rs.getInt(1);
			}
			return count;
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("", e);
			return 0;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
					logger.error("", e);
				}
			}
			if (countStmt != null) {
				try {
					countStmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
					logger.error("", e);
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
					logger.error("", e);
				}
			}
		}
	}

	/**
	 * excludeOrderBy:去除sql中的order by，避免大数据分页查询时的性能问题. <br/>
	 *
	 * @author yuanchao
	 * @param sql
	 * @return
	 */
	private static String excludeOrderBy(String sql) {
		if (StringUtils.isBlank(sql)) {
			return sql;
		}
//		sql = sql.toLowerCase();
//		sql = sql.replaceAll("[\\s]+", " ").replaceAll("[\\s]+,", ",").replaceAll(",[\\s]+", ",").replaceAll("[\\s]desc", "desc")
//				.replaceAll("desc[\\s]", "desc").replaceAll("[\\s]asc", "asc").replaceAll("asc[\\s]", "asc");
//		sql = sql.replaceAll("\\)", " ) ").replaceAll("\\(", " ( ");
//		String[] strArray = sql.split(" ");
//		String[] strCp = strArray;
//		StringBuffer resultSql = new StringBuffer();
//		for (int i = 0; i < strArray.length; i++) {
//			if ("order".equals(strArray[i].trim()) && "by".equals(strArray[i + 1].trim())) {
//				strCp[i] = " ";
//				strCp[i + 1] = " ";
//				strCp[i + 2] = " ";
//			}
//			if ("desc".equals(strArray[i].trim()) || "asc".equals(strArray[i].trim())) {
//				strCp[i] = " ";
//			}
//		}
//		for (String str : strCp) {
//			resultSql.append(str + " ");
//		}

		Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*",
				Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(sql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
        if(sb.length() == 0){
            return sql ;    
        }
		return sb.toString();
	}

	/**
	 * 根据数据库方言，生成特定的分页sql。
	 *
	 * @param sql     Mapper中的Sql语句
	 * @param page    分页对象
	 * @param dialect 方言类型
	 * @return 分页SQL
	 */
	public static String generatePageSql(String sql, Pagination<Serializable> page, Dialect dialect) {
		if (dialect.supportsLimit()) {
			int pageSize = page.getPageSize();
			int index = (page.getCurrentPage() - 1) * pageSize;
			int start = index < 0 ? 0 : index;
			return dialect.getLimitString(sql, start, pageSize);
		} else {
			return sql;
		}
	}
}
